﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace YMImportWeb.Common
{
    public class DBHelper
    {
        //获取连接字符串
        private static readonly string connectionString = ConfigurationManager.AppSettings["DbHelperConnectionString"].ToString();

        /// <summary>
        /// 执行查询，返回一张表
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="parameter">传给SQL的参数集合（可选）</param>
        /// <returns>返回一个DataTable类型的值</returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] parameter)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                AddParameter(cmd, parameter);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 执行查询，返回一个阅读器
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameter">传给SQL的参数集合（可选）</param>
        /// <returns>返回一个SqlDataReader类型的值</returns>
        public static SqlDataReader GetDataReader(string sql, SqlParameter[] parameter)
        {
            SqlDataReader dr = null;
            SqlConnection conn = new SqlConnection(connectionString);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                AddParameter(cmd, parameter);
                dr = cmd.ExecuteReader();
            }
            catch
            {
                dr.Dispose();
                conn.Close();
            }
            finally
            {
                dr.Dispose();
                conn.Close();
            }
            return dr;
        }

        /// <summary>
        /// 执行查询，返回第一行第一列的值
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameter">传给SQL的参数集合（可选）</param>
        /// <returns>返回一个object类型的值</returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameter)
        {
            object oj = new object();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                AddParameter(cmd, parameter);
                oj = cmd.ExecuteScalar();
            }
            return oj;
        }

        /// <summary>
        /// 执行操作（增、删、改），返回受影响的行
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameter">传给SQL的参数集合（可选）</param>
        /// <returns>返回一个int类型的值</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameter)
        {
            int result = 0;
            SqlTransaction tran = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    //开始事务
                    tran = conn.BeginTransaction();
                    SqlCommand cmd = new SqlCommand(sql, conn, tran);
                    AddParameter(cmd, parameter);
                    int n = cmd.ExecuteNonQuery();
                    if (n > 0)
                    {
                        //提交事务
                        tran.Commit();
                        result = 1;
                    }
                    else
                    {
                        //回滚事务
                        tran.Rollback();
                        result = 0;
                    }
                }
                catch (Exception)
                {
                    //回滚事务
                    tran.Rollback();
                    result = 0;
                }                
            }
            return result;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameter">传给SQL的参数集合（可选）</param>
        /// <returns></returns>
        public static DataTable ExecuteStoredProcedure(string procName, params SqlParameter[] parameter)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(procName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                AddParameter(cmd, parameter);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 遍历parameters参数，将其添加到command参数集合中
        /// </summary>
        /// <param name="cmd">command实例</param>
        /// <param name="parameter">传给SQL的参数集合</param>
        private static void AddParameter(SqlCommand cmd, SqlParameter[] parameters)
        {
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if (parameter == null)
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
    }
}